package com.sg.hms.DaoImpl;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;

import net.sf.json.JSONArray;
import net.sf.json.JSONObject;

import com.sg.hms.Dao.DepartmentDao;
import com.sg.hms.Dao.EmployeeDao;
import com.sg.hms.Model.Department;
import com.sg.hms.Model.Employee;
import com.sg.hms.Model.User;
import com.sg.hms.Util.JDBCUtil;

public class EmployeeDaoImpl implements EmployeeDao{
    DepartmentDao dDao=new DepartmentDaoImpl();
	@Override
	public Employee selectEmployeeByElId(int elId) {
		// TODO Auto-generated method stub
		Employee em=null;
		Connection con=JDBCUtil.getConnection();
		StringBuffer sql=new StringBuffer("select * from employee where elId=?");
		try {
			PreparedStatement pst=con.prepareStatement(sql.toString());
			pst.setInt(1,elId);
			ResultSet rs=pst.executeQuery();
			if(rs.next()){
				em=new Employee();
				em.setElId(rs.getInt("elId"));
				em.setElName(rs.getString("elName"));
				em.setdId(rs.getInt("dId"));
				em.setElSex(rs.getString("elSex"));
				em.setElAge(rs.getInt("elAge"));
				//日期转换
				Date date=rs.getDate("enterTime");
				java.util.Date d=new java.util.Date(date.getTime());
				SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
				String strdate=sdf.format(d);
				em.setEnterTime(strdate);
				em.setTellphone(rs.getString("tellphone"));
				em.setSalary(rs.getDouble("salary"));
				em.setState(rs.getString("state"));
			}
			pst.close();
			con.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return em;
	}
	//返回部门下的员工
	@Override
	public JSONArray selectEmployeeBydName(String dName) {
		// TODO Auto-generated method stub
		JSONArray employees = new JSONArray();
		StringBuffer sql = new StringBuffer("select elId,elName,department.dId,dName from employee,department  ");
		sql.append("where employee.dId = department.dId and dName like ?");
		Connection conn = JDBCUtil.getConnection();
		try {
			PreparedStatement pst = conn.prepareStatement(sql.toString());
			pst.setString(1, "%"+dName+"%");
			ResultSet set = pst.executeQuery();
			while (set.next()) {				
				JSONObject jsonObject = new JSONObject();
				jsonObject.put("elId",set.getInt("elId"));
				jsonObject.put("elName",set.getString("elName"));
				jsonObject.put("dId",set.getInt("elId"));
				jsonObject.put("dName", set.getString("dName"));
				employees.add(jsonObject);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return employees;
	}

	@Override
	public List<Employee> selectEmployeeByGroup(int start,int rows ) {
		// TODO Auto-generated method stub
		List<Employee> em=new ArrayList<Employee>();
		Connection con=JDBCUtil.getConnection();
		StringBuffer sql=new StringBuffer("SELECT elId,elName,elSex,elAge,tellphone,enterTime,salary,state,dId FROM employee LIMIT ?,?");
		try {
			PreparedStatement pst=con.prepareStatement(sql.toString());
			pst.setInt(1, start);
			pst.setInt(2, rows);
			ResultSet rs=pst.executeQuery();
			while(rs.next()){
				Employee employee=new Employee();
				employee.setElId(rs.getInt("elId"));
				employee.setElName(rs.getString("elName"));
				employee.setElSex(rs.getString("elSex"));
				employee.setElAge(rs.getInt("elAge"));
				employee.setTellphone(rs.getString("tellphone"));
				employee.setEnterTime(rs.getString("enterTime"));
				employee.setSalary(rs.getDouble("salary"));
				employee.setState(rs.getString("state"));
				employee.setdId(rs.getInt("dId"));
				String dId=dDao.selectDeNameBydId(rs.getInt("dId"));
				employee.setdName(dId);
				em.add(employee);
			}
			pst.close();
			con.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return em;
	}
	
	@Override
	public List<Employee> selectEmployeeByGroup(int start, int rows, String searchName) {
		// TODO Auto-generated method stub
		List<Employee> em=new ArrayList<Employee>();
		Connection con=JDBCUtil.getConnection();
		StringBuffer sql=new StringBuffer("SELECT elId,elName,elSex,elAge,tellphone,enterTime,salary,state,employee.dId FROM employee,department");
		sql.append(" WHERE employee.dId=department.dId AND CONCAT(dName,elName)like ? LIMIT ?,?");
		try {
			PreparedStatement pst=con.prepareStatement(sql.toString());
			pst.setString(1, "%"+searchName+"%");
			pst.setInt(2, start);
			pst.setInt(3, rows);
			ResultSet rs=pst.executeQuery();
			while(rs.next()){
				Employee employee=new Employee();
				employee.setElId(rs.getInt("elId"));
				employee.setElName(rs.getString("elName"));
				employee.setElSex(rs.getString("elSex"));
				employee.setElAge(rs.getInt("elAge"));
				employee.setTellphone(rs.getString("tellphone"));
				employee.setEnterTime(rs.getString("enterTime"));
				employee.setSalary(rs.getDouble("salary"));
				employee.setState(rs.getString("state"));
				employee.setdId(rs.getInt("dId"));
				String dId=dDao.selectDeNameBydId(rs.getInt("dId"));
				employee.setdName(dId);
				em.add(employee);
			}
			pst.close();
			con.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return em;
	}

	@Override
	public int selectEmployeeCount() {
		// TODO Auto-generated method stub
		int countEmpl=0;
		Connection con=JDBCUtil.getConnection();
		StringBuffer sql=new StringBuffer("SELECT COUNT(elId) countEmpl FROM employee");
		try {
			PreparedStatement pst=con.prepareStatement(sql.toString());
			ResultSet rs=pst.executeQuery();
			if(rs.next()){
				countEmpl=rs.getInt("countEmpl"); 
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return countEmpl;
	}
	@Override
	public int selectEmployeeCount(String searchName) {
		// TODO Auto-generated method stub
		int countEmpl=0;
		Connection con=JDBCUtil.getConnection();
		StringBuffer sql=new StringBuffer("SELECT count(elId) countEmpl  FROM employee,department ");
		sql.append("WHERE employee.dId=department.dId AND CONCAT(dName,elName)like ?");
		try {
			PreparedStatement pst=con.prepareStatement(sql.toString());
			pst.setString(1, "%"+searchName+"%");
			ResultSet rs=pst.executeQuery();
			if(rs.next()){
				countEmpl=rs.getInt("countEmpl");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return countEmpl;
	}

	@Override
	public int addEmployee(Employee em) {
		// TODO Auto-generated method stub
		int flag=0;
		Connection con=JDBCUtil.getConnection();
		StringBuffer sql=new StringBuffer("INSERT INTO employee SET elName=?,elSex=?,elAge=?,tellphone=?,enterTime=?,salary=?,state=?,dId=?");
		try {
			PreparedStatement pst=con.prepareStatement(sql.toString());
			pst.setString(1, em.getElName());
			pst.setString(2, em.getElSex());
			pst.setInt(3, em.getElAge());
			pst.setString(4, em.getTellphone());
			pst.setString(5, em.getEnterTime());
			pst.setDouble(6, em.getSalary());
			pst.setString(7, em.getState());
			pst.setInt(8, em.getdId());
			flag=pst.executeUpdate();
			if(flag>0){
				flag=1;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return flag;
	}

	@Override
	public int upEmployee(Employee em) {
		// TODO Auto-generated method stub
		int flag=0;
		Connection con=JDBCUtil.getConnection();
		StringBuffer sql=new StringBuffer("UPDATE employee SET elName=?,elSex=?,elAge=?,tellphone=?,enterTime=?,salary=?,state=?,dId=? WHERE elId=?");
		try {
			PreparedStatement pst=con.prepareStatement(sql.toString());
			pst.setString(1, em.getElName());
			pst.setString(2, em.getElSex());
			pst.setInt(3, em.getElAge());
			pst.setString(4, em.getTellphone());
			pst.setString(5, em.getEnterTime());
			pst.setDouble(6, em.getSalary());
			pst.setString(7, em.getState());
			pst.setInt(8, em.getdId());
			pst.setInt(9, em.getElId());
			flag=pst.executeUpdate();
			if(flag>0){
				flag=1;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return flag;
	}

	@Override
	public int delEmployee(int elId) {
		// TODO Auto-generated method stub
		int flag=0;
		Connection con=JDBCUtil.getConnection();
		StringBuffer sql=new StringBuffer("DELETE FROM employee WHERE elId=?");
		try {
			PreparedStatement pst=con.prepareStatement(sql.toString());
			pst.setInt(1, elId);
			flag=pst.executeUpdate();
			if(flag>0){
				flag=1;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return flag;
	}

	@Override
	public List<Employee> SortEmpl(String sort, String order, int start, int rows) {
		// TODO Auto-generated method stub
		List<Employee> em=new ArrayList<Employee>();
		Connection con=JDBCUtil.getConnection();
		//StringBuffer sql=new StringBuffer("select * from employee order by ？？ LIMIT ?,?")
		//上面的方式sql出错，setString自动加上了单引号
		StringBuffer sql=new StringBuffer("select * from employee order by "+sort+" "+order+" LIMIT ?,?");
		try {
			PreparedStatement pst=con.prepareStatement(sql.toString());
			pst.setInt(1, start);
			pst.setInt(2, rows);
			ResultSet rs=pst.executeQuery();
			while(rs.next()){
				Employee employee=new Employee();
				employee.setElId(rs.getInt("elId"));
				employee.setElName(rs.getString("elName"));
				employee.setElSex(rs.getString("elSex"));
				employee.setElAge(rs.getInt("elAge"));
				employee.setTellphone(rs.getString("tellphone"));
				employee.setEnterTime(rs.getString("enterTime"));
				employee.setSalary(rs.getDouble("salary"));
				employee.setState(rs.getString("state"));
				employee.setdId(rs.getInt("dId"));
				String dId=dDao.selectDeNameBydId(rs.getInt("dId"));
				employee.setdName(dId);
				em.add(employee);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return em;
	}

	@Override
	public int personalUp(Employee em) {
		// TODO Auto-generated method stub
		int flag=0;
		Connection con=JDBCUtil.getConnection();
		StringBuffer sql=new StringBuffer("UPDATE employee SET elName=?,elSex=?,elAge=?,tellphone=? WHERE elId=?");
		try {
			PreparedStatement pst=con.prepareStatement(sql.toString());
			pst.setString(1, em.getElName());
			pst.setString(2, em.getElSex());
			pst.setInt(3, em.getElAge());
			pst.setString(4, em.getTellphone());
			pst.setInt(5, em.getElId());
			flag=pst.executeUpdate();
			if(flag>0){
				flag=1;
			}
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return flag;
	}
	@Override
	public List<Employee> SortEmpl(String searchName, String sort, String order, int start, int rows) {
		// TODO Auto-generated method stub
				List<Employee> em=new ArrayList<Employee>();
				Connection con=JDBCUtil.getConnection();
				StringBuffer sql=new StringBuffer("SELECT elId,elName,elSex,elAge,tellphone,enterTime,salary,state,employee.dId FROM employee,department ");
				sql.append(" WHERE employee.dId=department.dId AND CONCAT(dName,elName)like ? order by "+sort+" "+order+" LIMIT ?,?");
				try {
					PreparedStatement pst=con.prepareStatement(sql.toString());
					pst.setString(1, "%"+searchName+"%");
					pst.setInt(2, start);
					pst.setInt(3, rows);
					ResultSet rs=pst.executeQuery();
					while(rs.next()){
						Employee employee=new Employee();
						employee.setElId(rs.getInt("elId"));
						employee.setElName(rs.getString("elName"));
						employee.setElSex(rs.getString("elSex"));
						employee.setElAge(rs.getInt("elAge"));
						employee.setTellphone(rs.getString("tellphone"));
						employee.setEnterTime(rs.getString("enterTime"));
						employee.setSalary(rs.getDouble("salary"));
						employee.setState(rs.getString("state"));
						employee.setdId(rs.getInt("dId"));
						String dId=dDao.selectDeNameBydId(rs.getInt("dId"));
						employee.setdName(dId);
						em.add(employee);
					}
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
				return em;
	}

}
